package com.qkjia.zqkspringshell.mysql.command;



import com.qkjia.zqkspringshell.mysql.utils.DBMysqlUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.shell.Availability;
import org.springframework.shell.standard.ShellComponent;
import org.springframework.shell.standard.ShellMethod;
import org.springframework.shell.standard.ShellOption;


@ShellComponent
@Slf4j
public class MysqlCommands {

    @Autowired
    private ConfigurableApplicationContext configurableApplicationContext;


    private static DBMysqlUtil dbMysqlUtil = null;

    private boolean connected = false;


 /*   @ShellMethod(value = "连接数据库参数直接回车填写 之后测试", group = "工具类连接")
    @ShellMethodAvailability({"createdatabase"})
    public Availability connect() {
        //String driverClass, String url, String sqlusername, String password
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入driverClass： 如：com.mysql.cj.jdbc.Driver");
        String driverClass = scanner.nextLine();
        System.out.println("请输入url  如: jdbc:mysql://localhost:3306/mybase?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC");
        String url = scanner.nextLine();
        System.out.println("请输入user 如：root");
        String sqlusername = scanner.nextLine();
        System.out.println("请输入password：如：123456");
        String password = scanner.nextLine();
        try {
            dbMysqlUtil = new DBMysqlUtil(driverClass,url,sqlusername,password);
            dbMysqlUtil.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            return connected ? Availability.available()
                    : Availability.unavailable("你没连接");
        }

        return connected ? Availability.available()
                : Availability.unavailable("你没连接");
    }*/

    //写IP + 端口
    @ShellMethod(value = "连接数据库 直接回车", group = "工具类连接")
    public  Availability connect(
            @ShellOption(defaultValue = "root") String sqlusername,//root
            @ShellOption(defaultValue = "123456") String password,
            @ShellOption(defaultValue = "com.mysql.cj.jdbc.Driver") String driverClass,
            @ShellOption(defaultValue = "jdbc:mysql://localhost:3306/mybase?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC") String url//
//            @NotEmpty String url,//jdbc:mysql://localhost:3306?serverTimezone=UTC
//            @NotEmpty String driverClass
    ) {//com.mysql.cj.jdbc.Driver

        try {
            dbMysqlUtil = new DBMysqlUtil(driverClass,url,sqlusername,password);
            dbMysqlUtil.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            return connected ? Availability.available()
                    : Availability.unavailable("你没连接");
        }

        return connected ? Availability.available()
                : Availability.unavailable("你没连接");
    }


    @ShellMethod(value = "去除数据库连接,回车直接用", group = "工具类连接")
    public String removeconnect() {
        dbMysqlUtil = null;
//        dbMysqlUtil.close();
        return "成功";
    }


    /*  4、执行SQL
    executeUpdate()：实现增删改，返回值是int类型的结果，表示受影响的行数
    executeQuery()：实现查询*/

    @ShellMethod(value = "所有的增删改  [--sql sql] sql外加”“", group = "工具类")
    public String insertorUpdateorDelete(String sql) {
        System.out.println("执行SQL：" + sql);
        //"insert into t_student(name,age) values('zhaoliu',30);"
        //"update t_student set  age=50 where name ='lisi';"
        //"delete from  t_student where name ='zhaoliu';"
        try {
            dbMysqlUtil.createdatabase(sql);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }


    @ShellMethod(value = "所有的查询  [--sql sql] sql外加”“", group = "工具类")
    public String allSelect(String sql) {
        System.out.println("执行SQL：" + sql);
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.allSelect(sql);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "查询建表语句  [--Tablename Tablename] ", group = "工具类获取表信息")
    public String selectCreateTable(String tableName) {
        //SELECT * FROM t_student
        System.out.println(tableName);
        try {
            dbMysqlUtil.createTableDDl(tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }



    //
    @ShellMethod(value = "查看数据库版本 回车查看", group = "工具类数据库信息")
    public String mysqlVersionAndTime() {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.mysqlVersionAndTime();
        } catch (Exception e) {
//            if (dbMysqlUtil==null) {
//                return "请连接数据库";
//            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获取表中所有列字段名称  [--Tablename Tablename] ", group = "工具类获取表信息")
    public String getColumnNames(String tableName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getColumnNames(tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获取表中所有字段类型  [--Tablename Tablename] ", group = "工具类获取表信息")
    public String getColumnTypes(String tableName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getColumnTypes(tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获取表中所有字段注释  [--Tablename Tablename] ", group = "工具类获取表信息")
    public String getColumnComments(String tableName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getColumnComments(tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获取表中索引信息  [--Tablename Tablename] ", group = "工具类获取表信息")
    public String checkIndex(String tableName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getIndexInfo(tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获取数据库相关信息，直接回车就行", group = "工具类数据库信息")
    public String getDataBaseInformations() {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getDataBaseInformations();
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }


    @ShellMethod(value = "获得数据库中所有方案名称  直接回车获取", group = "工具类数据库信息")
    public String getAllSchemas() {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getAllSchemas();
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }


    @ShellMethod(value = "得表或视图中的所有列信息    [--tableName tableName]", group = "工具类获取表信息")
    public String getTableColumns(String tableName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getTableColumns(tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获得该用户下面的所有视图   [--schemaName schemaName]", group = "工具类数据库信息")
    public String getAllViewList(String schemaName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getAllViewList(schemaName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }


    @ShellMethod(value = "获得一个表的主键信息   [--schemaName schemaName] [--tableName tableName]", group = "工具类获取表信息")
    public String getAllPrimaryKeys(String schemaName, String tableName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getAllPrimaryKeys(schemaName,tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获得一个表的外键信息   [--schemaName schemaName] [--tableName tableName]", group = "工具类获取表信息")
    public String getAllExportedKeys(String schemaName, String tableName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.getAllExportedKeys(schemaName,tableName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }

    @ShellMethod(value = "获得一个表结构   [--tableName tableName]", group = "工具类获取表信息")
    public String describeTable(String schemaName) {
        //SELECT * FROM t_student
        try {
            dbMysqlUtil.describeTable(schemaName);
        } catch (Exception e) {
            if (dbMysqlUtil==null) {
                return "请连接数据库";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "主人操作成功了";
    }
}
